const sql = require('mssql');

/**
 * 
 * 平板拣货系统后台
 */
// 拣货开始时间
let startTime = '2019-04-15 00:00:01.000';
// let startTime = '2018-07-01 00:00:01.000';
/**
 * 
 * 拣货系统登录 
 */
const pickGoodsLogin = async(ctx, next) => {
        const body = ctx.request.body;
        let selectSql = `SELECT DLMC,BM,MC FROM BM_YG WHERE DLMC = '${body.username}' AND MM = '${body.password}'`;
        // let selectSql = `SELECT username,id FROM JHY WHERE username = '${body.username}' AND password = '${body.password}'`;
        let result = await sql.query(selectSql);
        if (result.rowsAffected[0] === 1) {
            ctx.body = { success: true, result: result.recordset[0] }
        } else {
            ctx.body = { success: false }
        }
    }
    /**
     * 
     * 获取接单信息 
     */
const getPickGoodsOrder = async(ctx, next) => {
    const body = ctx.request.body;
    let distributionInfo = await sql.query(`SELECT * FROM YG_QYFP WHERE BM = '${body.userId}'`);
    let distributionInfoResult = distributionInfo.recordset;
    let actionSql = `SELECT X.ID,X.RQ,X.DWBM,X.DWMC,X.JSFSMC,X.ZJE,B.DH FROM XSD_ZB X LEFT JOIN BM_WLDW B ON X.DWBM = B.BM WHERE X.SH = 'T' AND X.ID_DHD <> '' AND X.ID <> ALL (SELECT ID FROM FPDD)`;
    // 分配拣货区域且状态是启用
    if ((0 in distributionInfoResult) && distributionInfoResult[0].status === 'T') {

        //     if(distributionInfoResult[0].area === 1){
        //           // 分配的是单订单阴凉库
        //         actionSql += ` AND X.ID IN (SELECT T1.ID FROM (SELECT COUNT(ID) AS PX,ID FROM XSD_MX  GROUP BY ID ) T1 LEFT JOIN (SELECT HWBH,ID FROM XSD_MX ) T2 ON T1.ID = T2.ID  WHERE T1.PX = 1 AND T2.HWBH LIKE '__L%') `;
        //     } else if(distributionInfoResult[0].area === 2){
        //           // 分配的是单订单常温库
        //         actionSql += ` AND X.ID IN (SELECT T1.ID FROM (SELECT COUNT(ID) AS PX,ID FROM XSD_MX  GROUP BY ID ) T1 LEFT JOIN (SELECT HWBH,ID FROM XSD_MX ) T2 ON T1.ID = T2.ID  WHERE T1.PX = 1 AND T2.HWBH LIKE '__W%') `;
        //     }else if(distributionInfoResult[0].area === 3){ 
        //           // 分配的是多订单所有库
        //         actionSql += ` AND X.ID IN (SELECT ID FROM (SELECT COUNT(ID) AS PX,ID FROM XSD_MX GROUP BY ID ) T1 WHERE T1.PX > 1) `;
        //     }else if(distributionInfoResult[0].area === 4){
        //         // 分配的是单订单所有库
        //       actionSql += ` AND X.ID IN (SELECT ID FROM (SELECT COUNT(ID) AS PX,ID FROM XSD_MX GROUP BY ID ) T1 WHERE T1.PX = 1) `;
        //   }


        if (distributionInfoResult[0].area === 1) {
            // 分配的是阴凉库
            actionSql += ` AND X.ID IN (SELECT ID FROM XSD_MX WHERE HWBH LIKE '__L%') AND X.ID <>ALL (SELECT ID FROM XSD_MX WHERE HWBH LIKE '__W%') `;
        } else if (distributionInfoResult[0].area === 2) {
            // 分配的是常温库
            actionSql += ` AND X.ID IN (SELECT ID FROM XSD_MX WHERE HWBH LIKE '__W%') AND X.ID <>ALL (SELECT ID FROM XSD_MX WHERE HWBH LIKE '__L%') `;
        }

    }
    actionSql += ` AND X.RQ >='${startTime}'  ORDER BY CONVERT(varchar(10), X.RQ, 120 ) ,CASE X.JSFSBM WHEN '15' THEN 1  WHEN '11' THEN 2  WHEN '13' THEN 3  WHEN '12' THEN 4 END `;
    let result = await sql.query(actionSql);

    if (result.rowsAffected[0] > 0) {
        let insertGotOrderSql = `INSERT INTO FPDD VALUES ('${result.recordset[0].ID}')`;
        sql.query(insertGotOrderSql)
        let selectShopSql = `SELECT YPBM,YPMC,YPGG,SL,SCPH ,SCCJ FROM XSD_MX WHERE ID = '${result.recordset[0].ID}'`;
        let allShop = await sql.query(selectShopSql);
        let shopCountSql = `SELECT SUM(SL) AS shopCount FROM XSD_MX WHERE ID = '${result.recordset[0].ID}' GROUP BY ID`;
        let shopCount = await sql.query(shopCountSql);
        // if (result.rowsAffected[0] > 0) {

        // }
        let time = setTimeout(async() => {
            let isGetOrderSql = `SELECT COUNT(*) as num FROM YHDD WHERE id = '${result.recordset[0].ID}'`;
            let isGetOrder = await sql.query(isGetOrderSql);
            if (isGetOrder.recordset[0].num == 0) {
                let deleteSql = `DELETE FROM FPDD WHERE ID = '${result.recordset[0].ID}'`
                sql.query(deleteSql);
            }
            clearTimeout(time);
        }, 300000)

        ctx.body = { success: true, result: result.recordset[0], allShop: allShop.recordset, shopCount: shopCount.recordset[0].shopCount }
    } else {
        ctx.body = { success: false }
    }
}

/**
 * 获得订单
 *  */
const havePickGoodsOrder = async(ctx, next) => {
    const body = ctx.request.body;
    let regCarNum = /^[A-Z]\d{3}$/g;
    if (regCarNum.test(body.carNum)) {
        let isExistSql = `SELECT COUNT(id) AS num FROM YHDD WHERE id = '${body.orderId}'`;
        let isExistResult = await sql.query(isExistSql);

        if (isExistResult.recordset[0].num == 0) {
            let selectShopSql = `SELECT YPBM,SCPH FROM XSD_MX WHERE ID = '${body.orderId}'`;
            let allShop = await sql.query(selectShopSql);
            let insertSql = `INSERT INTO YHDD (userid,id,carid,YPBM,JHZT,SCPH,FHZT,SFYC,JHRQ,FHRQ) `;
            allShop.recordset.forEach((item, ind) => {
                if (ind == allShop.recordset.length - 1) {
                    insertSql += `SELECT '${body.userId}','${body.orderId}','${body.carNum}','${item.YPBM}','F','${item.SCPH}','F','F','','' `
                } else {
                    insertSql += `SELECT '${body.userId}','${body.orderId}','${body.carNum}','${item.YPBM}','F','${item.SCPH}','F','F','','' UNION `
                }
            });
            let result = await sql.query(insertSql);
            if (result.rowsAffected[0] > 0) {
                ctx.body = { success: true }
            } else {
                ctx.body = { success: false }
            }
        } else {
            ctx.body = { success: false }
        }
    } else {
        ctx.body = { success: false }
    }
}

/**
 * 获得接单员的所有已接订单
 */
const getPickGoodsUserAllOrder = async(ctx, next) => {
    const body = ctx.request.body;
    let selectSql = `SELECT t1.id,t1.carid,X.RQ,X.DWBM,X.DWMC,X.JSFSMC,X.ZJE,B.DH FROM (SELECT id, carid  FROM YHDD WHERE userid = '${body.userId}' AND JHZT = 'F' AND id <>ALL (SELECT id FROM YHDD WHERE SFYC = 'T') GROUP BY id,carid) as t1 LEFT JOIN XSD_ZB X ON t1.id = X.ID LEFT JOIN BM_WLDW B ON X.DWBM = B.BM`;
    let result = await sql.query(selectSql);
    if (result.rowsAffected[0] > 0) {
        ctx.body = { success: true, result: result.recordset }
    } else {
        ctx.body = { success: false }
    }
}


/**
 * 获得接单员的所有已接订单的商品信息
 */
const getPickGoodsUserAllOrderShop = async(ctx, next) => {
    const body = ctx.request.body;
    let selectSql = `SELECT Y.ID,Y.YPBM,X.JLDW,X.YPMC,X.YPGG,X.SCCJ,SUM(X.SL) AS SL,Y.SCPH,X.YXQZ,X.HWBH,Y.carid,H.FQBH  FROM YHDD Y LEFT JOIN  XSD_MX X ON Y.id = X.id AND Y.YPBM = X.YPBM AND Y.SCPH = X.SCPH LEFT JOIN HWBH H ON X.HWBH = H.HWBH WHERE Y.userid = '${body.userId}' AND Y.JHZT = 'F' AND Y.SFYC = 'F' GROUP BY Y.ID,Y.YPBM,X.JLDW,X.YPMC,X.YPGG,X.SCCJ,X.SL,Y.SCPH,X.YXQZ,X.HWBH,Y.carid,H.FQBH`;
    let selectAllSql = `SELECT Y.ID,Y.YPBM,X.JLDW,X.YPMC,X.YPGG,X.SCCJ,SUM(X.SL) AS SL,Y.SCPH,X.YXQZ,X.HWBH,Y.carid,H.FQBH,Y.JHZT,Y.JHRQ  FROM YHDD Y LEFT JOIN  XSD_MX X ON Y.id = X.id AND Y.YPBM = X.YPBM AND Y.SCPH = X.SCPH LEFT JOIN HWBH H ON X.HWBH = H.HWBH WHERE Y.userid = '${body.userId}' AND Y.FHZT = 'F' AND Y.SFYC = 'F' GROUP BY Y.ID,Y.YPBM,X.JLDW,X.YPMC,X.YPGG,X.SCCJ,X.SL,Y.SCPH,X.YXQZ,X.HWBH,Y.carid,H.FQBH,Y.JHZT,Y.JHRQ `;
    // let selectSql = `SELECT X.ID,X.YPBM,X.JLDW,X.YPMC,X.YPGG,X.SCCJ,SUM(X.SL) AS SL,X.SCPH,X.YXQZ,X.HWBH,Y.carid,H.FQBH FROM  XSD_MX X LEFT JOIN HWBH H ON X.HWBH = H.HWBH LEFT JOIN YHDD Y ON X.YPBM = Y.YPBM AND X.SCPH = Y.SCPH  WHERE X.ID IN (SELECT id FROM YHDD WHERE userid = '${body.userId}') AND Y.JHZT = 'F' GROUP BY X.ID,X.YPBM,X.JLDW,X.YPMC,X.YPGG,X.SCCJ,X.SL,X.SCPH,X.YXQZ,X.HWBH,Y.carid,H.FQBH`;
    // let selectSql = `SELECT X.ID,X.YPBM,X.JLDW,X.YPMC,X.YPGG,X.SCCJ,X.SL,X.SCPH,X.YXQZ,X.HWBH,Y.carid,H.HWQH FROM  XSD_MX X LEFT JOIN HWBH H ON X.HWBH = H.HWBH LEFT JOIN YHDD Y ON X.YPBM = Y.YPBM AND X.SCPH = Y.SCPH  WHERE X.ID IN (SELECT id FROM YHDD WHERE userid = ${body.userId}) AND Y.JHZT = 'F'`;

    let result = await sql.query(selectSql);
    let resultAll = await sql.query(selectAllSql);
    if (result.rowsAffected[0] > 0) {
        ctx.body = { success: true, result: result.recordset, resultAll: resultAll.recordset }
    } else {
        ctx.body = { success: false }
    }
}

/**
 * 
 *格式化日期
 */
const dateFomat = (fmt, date) => {
        var o = {
            "M+": date.getMonth() + 1, //月份   
            "d+": date.getDate(), //日   
            "h+": date.getHours(), //小时   
            "m+": date.getMinutes(), //分   
            "s+": date.getSeconds(), //秒   
            "q+": Math.floor((date.getMonth() + 3) / 3), //季度   
            "S": date.getMilliseconds() //毫秒   
        };
        if (/(y+)/.test(fmt))
            fmt = fmt.replace(RegExp.$1, (date.getFullYear() + "").substr(4 - RegExp.$1.length));
        for (var k in o)
            if (new RegExp("(" + k + ")").test(fmt))
                fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (o[k]) : (("00" + o[k]).substr(("" + o[k]).length)));
        return fmt + '.000';
    }
    /**
     * 开始拣货
     */
const pickGoods = async(ctx, next) => {
    const body = ctx.request.body;
    console.log('body', body)
    let updateSql = `UPDATE YHDD SET JHZT = 'T',JHRQ = '${dateFomat("yyyy-MM-dd hh:mm:ss", new Date())}' WHERE userid = '${body.userId}' AND id = '${body.orderId}' AND YPBM = '${body.ypbm}' AND SCPH = '${body.scph}'`;


    let sqlList = [];


    let result = await sql.query(updateSql);
    if (result.rowsAffected[0] > 0) {
        ctx.body = { success: true }
    } else {
        ctx.body = { success: false }
    }
}

/**
 * 改变异常订单的状态
 */
const setPickGoodsAbnormalOrder = async(ctx, next) => {
    const body = ctx.request.body;
    let insertSql = `INSERT INTO JHCY VALUES('${body.shopInfo.ID}','${body.person}','${body.reason}','${body.shopInfo.YPBM}','${body.shopInfo.SCPH}','${dateFomat("yyyy-MM-dd hh:mm:ss", new Date())}')`;
    let insertResult = await sql.query(insertSql);
    let updateSql = `UPDATE YHDD SET SFYC = 'T' WHERE userid = '${body.userId}' AND id = '${body.orderId}' AND YPBM = '${body.ypbm}' AND SCPH = '${body.scph}'`;
    // // let deleteSql = `DELETE FROM YHDD WHERE userid = ${body.userId} AND id = '${body.orderId}' AND HWBH = '${body.hwbh}'`;
    // // let selectSql = `SELECT X.ID,X.YPBM,X.YPMC,X.YPGG,X.SCCJ,X.SL,X.SCPH,X.YXQZ,X.HWBH,Y.carid,H.HWQH FROM  XSD_MX X LEFT JOIN HWBH H ON X.HWBH = H.HWBH LEFT JOIN YHDD Y ON X.HWBH = Y.HWBH  WHERE X.ID IN (SELECT id FROM YHDD WHERE userid = ${body.userId}) AND Y.JHZT = 'F'`;
    // // let result = await sql.query(deleteSql);
    let result = await sql.query(updateSql);
    if (result.rowsAffected[0] > 0) {
        ctx.body = { success: true }
    } else {
        ctx.body = { success: false }
    }
}

/**
 * 
 * 获得可接订单的数量
 */
const getPickGoodOrderCount = async(ctx, next) => {
    // AND X.RQ >='2019-04-01 00:00:00.000'
    // let selectSql = `SELECT COUNT(X.ID) AS total FROM XSD_ZB X LEFT JOIN BM_WLDW B ON X.DWBM = B.BM WHERE X.SH = 'T' AND X.ID_DHD <> '' AND X.ID <> ALL (SELECT ID FROM FPDD) AND  X.RQ >='2018-01-01 00:00:00.000' AND X.RQ <='2019-03-29 00:00:00.000' `;
    let selectSql = `SELECT COUNT(X.ID) AS total FROM XSD_ZB X LEFT JOIN BM_WLDW B ON X.DWBM = B.BM WHERE X.SH = 'T' AND X.ID_DHD <> '' AND X.ID <> ALL (SELECT ID FROM FPDD) AND X.RQ >='${startTime}' `;
    let result = await sql.query(selectSql);
    if (result.rowsAffected[0] > 0) {
        ctx.body = { success: true, result: result.recordset[0].total }
    } else {
        ctx.body = { success: false }
    }
}

/**
 * 判断购物车是否被占用
 */
const isUsedCarNum = async(ctx, next) => {
    const body = ctx.request.body;
    let selectSql = `SELECT COUNT(carid) AS car FROM YHDD WHERE FHZT = 'F' AND carid = '${body.carNum}'`;
    let result = await sql.query(selectSql);
    if (result.recordset[0].car > 0) {
        ctx.body = { success: false }
    } else {
        ctx.body = { success: true }
    }
}



module.exports = {
    'POST /pickGoodsLogin': pickGoodsLogin,
    'POST /getPickGoodsOrder': getPickGoodsOrder,
    'POST /havePickGoodsOrder': havePickGoodsOrder,
    'POST /getPickGoodsUserAllOrder': getPickGoodsUserAllOrder,
    'POST /getPickGoodsUserAllOrderShop': getPickGoodsUserAllOrderShop,
    'POST /pickGoods': pickGoods,
    'POST /isUsedCarNum': isUsedCarNum,
    'GET /getPickGoodOrderCount': getPickGoodOrderCount,
    'POST /setPickGoodsAbnormalOrder': setPickGoodsAbnormalOrder,

}